package com.ruoyi.operation.service.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.ClassUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Assert;
import com.ruoyi.common.core.exception.ServiceException;
import com.ruoyi.common.core.utils.StringUtils;
import com.ruoyi.common.mybatis.core.page.PageQuery;
import com.ruoyi.common.mybatis.core.page.TableDataInfo;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.ruoyi.common.satoken.utils.LoginHelper;
import com.ruoyi.common.workflow.util.BuildVariableUtil;
import com.ruoyi.operation.cmdb.CmdbConcurrenHelper;
import com.ruoyi.operation.domain.vo.ResTableVo;
import com.ruoyi.operation.domain.vo.TBusisystemVo;
import com.ruoyi.operation.service.ITBusisystemService;
import com.ruoyi.system.api.RemoteDeptService;
import com.ruoyi.system.api.RemoteSysConfigService;
import com.ruoyi.workflow.api.RemoteWorkFlowService;
import com.zaxxer.hikari.HikariDataSource;
import lombok.RequiredArgsConstructor;
import org.apache.dubbo.config.annotation.DubboReference;
import org.dromara.warm.flow.core.dto.FlowParams;
import org.dromara.warm.flow.core.entity.HisTask;
import org.dromara.warm.flow.core.entity.Instance;
import org.dromara.warm.flow.core.enums.FlowStatus;
import org.dromara.warm.flow.core.service.HisTaskService;
import org.dromara.warm.flow.core.service.InsService;
import org.dromara.warm.flow.core.utils.ExpressionUtil;
import org.dromara.warm.flow.core.utils.IdUtils;
import org.dromara.warm.flow.orm.entity.FlowHisTask;
import org.dromara.warm.flow.orm.entity.FlowInstance;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import com.ruoyi.operation.domain.bo.TSqltoolBo;
import com.ruoyi.operation.domain.vo.TSqltoolVo;
import com.ruoyi.operation.domain.TSqltool;
import com.ruoyi.operation.mapper.TSqltoolMapper;
import com.ruoyi.operation.service.ITSqltoolService;
import org.springframework.transaction.annotation.Transactional;

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.*;
import java.util.stream.Collectors;

/**
 * sql执行工具Service业务层处理
 *
 * @author ruoyi
 * @date 2025-06-18
 */
@RequiredArgsConstructor
@Service
public class TSqltoolServiceImpl implements ITSqltoolService {

    @Value("${cmdb.dbtype:15}")
    private String type;

    @Value("${cmdb.environment:prod}")
    private String environment;


    private final TSqltoolMapper baseMapper;
    private final InsService insService;
    private final HisTaskService hisTaskService;
    @DubboReference
    private RemoteDeptService deptService;
    @DubboReference
    private RemoteSysConfigService remoteSysConfigService;
    private final ITBusisystemService itBusisystemService;
    private final CmdbConcurrenHelper cmdbConcurrenHelper;

    private final String WORK_FLOW_SQL_CODE = "work.flow.sql.code";

    private final String WORK_FLOW_SQL_STATUS ="work.flow.sql.status";

    /**
     * 查询sql执行工具
     */
    @Override
    public TSqltoolVo queryById(String id){
        return baseMapper.selectVoById(id);
    }

    /**
     * 查询sql执行工具列表
     */
    @Override
    public TableDataInfo<TSqltoolVo> queryPageList(TSqltoolBo bo, PageQuery pageQuery) {
        LambdaQueryWrapper<TSqltool> lqw = buildQueryWrapper(bo);
        IPage<TSqltoolVo> result = baseMapper.selectVoPage(pageQuery.build(), lqw).convert(x->{
            String deptName = deptService.selectDeptNameByIds(x.getDeptid().toString());
            if (StrUtil.isNotBlank(x.getFlowCode())) {
                Instance instance = Optional.ofNullable(insService.getById(x.getFlowInstanceId())).orElseGet(FlowInstance::new);
                HisTask hisTask = hisTaskService.orderById().desc().list(new FlowHisTask().setInstanceId(Long.valueOf(x.getFlowInstanceId())))
                    .stream().findFirst().orElseGet(FlowHisTask::new);
                if(instance.getFlowStatus().equals(FlowStatus.FINISHED.getKey())){
                    x.setStatus(hisTask.getFlowStatus());
                }else{
                    x.setStatus(instance.getFlowStatus());
                }
            }
            TBusisystemVo vo = itBusisystemService.queryById(x.getBusisystemid());
            x.setBusisystemname(vo.getName());
            x.setDeptname(deptName);
            return x;
        });
        return TableDataInfo.build(result);
    }

    /**
     * 查询sql执行工具列表
     */
    @Override
    public List<TSqltoolVo> queryList(TSqltoolBo bo) {
        LambdaQueryWrapper<TSqltool> lqw = buildQueryWrapper(bo);
        return baseMapper.selectVoList(lqw);
    }

    private LambdaQueryWrapper<TSqltool> buildQueryWrapper(TSqltoolBo bo) {
        Map<String, Object> params = bo.getParams();
        LambdaQueryWrapper<TSqltool> lqw = Wrappers.lambdaQuery();
        lqw.eq(bo.getDeptid() != null, TSqltool::getDeptid, bo.getDeptid());
        lqw.eq(StringUtils.isNotBlank(bo.getBusisystemid()), TSqltool::getBusisystemid, bo.getBusisystemid());
        lqw.like(StringUtils.isNotBlank(bo.getToolname()), TSqltool::getToolname, bo.getToolname());
        lqw.eq(StringUtils.isNotBlank(bo.getMemo()), TSqltool::getMemo, bo.getMemo());
        lqw.eq(StringUtils.isNotBlank(bo.getDbtype()), TSqltool::getDbtype, bo.getDbtype());
        lqw.eq(StringUtils.isNotBlank(bo.getDburl()), TSqltool::getDburl, bo.getDburl());
        lqw.like(StringUtils.isNotBlank(bo.getDbusername()), TSqltool::getDbusername, bo.getDbusername());
        lqw.eq(StringUtils.isNotBlank(bo.getDbpassword()), TSqltool::getDbpassword, bo.getDbpassword());
        lqw.eq(StringUtils.isNotBlank(bo.getSqlcontent()), TSqltool::getSqlcontent, bo.getSqlcontent());
        lqw.eq(StringUtils.isNotBlank(bo.getStatus()), TSqltool::getStatus, bo.getStatus());
        return lqw;
    }

    /**
     * 新增sql执行工具
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean insertByBo(TSqltoolBo bo) {
        TSqltool add = BeanUtil.toBean(bo, TSqltool.class);
        validEntityBeforeSave(add);
        boolean flag = baseMapper.insert(add) > 0;
        if (flag) {
            bo.setId(add.getId());
        }
        return flag;
    }

    /**
     * 修改sql执行工具
     */
    @Override
    public Boolean updateByBo(TSqltoolBo bo) {
        TSqltool update = BeanUtil.toBean(bo, TSqltool.class);
        validEntityBeforeSave(update);
        return baseMapper.updateById(update) > 0;
    }

    /**
     * 保存前的数据校验
     */
    @Transactional(rollbackFor = Exception.class)
    public void validEntityBeforeSave(TSqltool entity){
        //校验,判断是否是SELECT类型，如果不是则不通过
        String sqlcontent = entity.getSqlcontent();
        if (!sqlcontent.toLowerCase().contains("select")) {
            throw new ServiceException("不允许查询以外的sql");
        }
        String string = remoteSysConfigService.selectConfigByKey(WORK_FLOW_SQL_STATUS);
        if (StringUtils.lowerCase(string).equals("true")) {
            String workFlow = remoteSysConfigService.selectConfigByKey(WORK_FLOW_SQL_CODE);
            entity.setFlowCode(workFlow);
            //
            String id = IdUtils.nextIdStr();
            if(StringUtils.isNotEmpty(entity.getId())){
                entity.setId(id);
            }
            //设定流程
            FlowParams flowParams = FlowParams.build().flowCode(workFlow);
            String userId = LoginHelper.getUserId() + "";
            flowParams.handler(userId);
            Map<String, Object> variable = BuildVariableUtil.buildVariable(entity);
            variable.put("businessType",workFlow);
            Map<String, String> businessData = new HashMap<>();
            businessData.put("SQL语句",entity.getSqlcontent());
            businessData.put("数据库地址",entity.getDburl());
            businessData.put("数据库用户名",entity.getDbusername());
            businessData.put("所属业务系统",itBusisystemService.queryById(entity.getBusisystemid()).getName());
            businessData.put("所属部门",deptService.selectDeptNameByIds(entity.getBusisystemid()));
            businessData.put("title","SQL审批");
            variable.put("businessData",businessData);
            flowParams.variable(variable);
            Instance instance = insService.start(id, flowParams);
            entity.setFlowInstanceId(instance.getId()+"");
        }
    }

    /**
     * 批量删除sql执行工具
     */
    @Override
    public Boolean deleteWithValidByIds(Collection<String> ids, Boolean isValid) {
        if(isValid){
            //TODO 做一些业务上的校验,判断是否需要校验
        }
        return baseMapper.deleteBatchIds(ids) > 0;
    }

    @Override
    public ResTableVo queryConfigSql(String name) {
        QueryWrapper<TSqltool> qw = new QueryWrapper<>();
        qw.lambda().eq(TSqltool::getToolname,name);
        TSqltool config = baseMapper.selectOne(qw);
        //如果开启了需要审批
        if(StringUtils.isNotEmpty(config.getFlowCode())){
            Instance instance = Optional.ofNullable(insService.getById(config.getFlowInstanceId())).orElseGet(FlowInstance::new);
            HisTask hisTask = hisTaskService.orderById().desc().list(new FlowHisTask().setInstanceId(Long.valueOf(config.getFlowInstanceId())))
                .stream().findFirst().orElseGet(FlowHisTask::new);
            if(instance.getFlowStatus().equals(FlowStatus.FINISHED.getKey())){
                Assert.isTrue(hisTask.getFlowStatus().equals(FlowStatus.PASS.getKey()),"流程未审批通过，无法执行");
            }else{
                Assert.isFalse(false,"流程未审批完成，无法执行");
            }
        }
        Assert.notNull(config,"Data source not found: " + name);
        HikariDataSource source = new HikariDataSource();
        source.setJdbcUrl(config.getDburl());
        source.setUsername(config.getDbusername());
        source.setPassword(config.getDbpassword());
        String dbtype = config.getDbtype();
        source.setDriverClassName(dbtype);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(source);
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(config.getSqlcontent());
        Map<String, Object> map = maps.get(0);
        ResTableVo table = new ResTableVo();
        table.setTableHeader(new ArrayList<>(map.keySet()));
        table.setTableData(maps);
        return table;
    }

    @Override
    public List<String> remoteUrl(String dbType) {
        List<Map<String, String>> res = new ArrayList<>();
        if(environment.equals("prod")){
            HashMap<String, Object> hashMap = new HashMap<>();
            hashMap.put("q","_type:"+type);
            hashMap.put("count",1000000);
            hashMap.put("page",1);
            res = cmdbConcurrenHelper.getCMDBData(hashMap);
        }else{
            String path = "D:\\IdeaProject\\RuoYi-Cloud-Plus\\ruoyi-modules\\ruoyi-operation\\src\\main\\java\\com\\ruoyi\\operation\\controller\\cmdb-dburl.json";
            try {
                String jsonContent = new String(Files.readAllBytes(Paths.get(path)));
                JSONObject jsonObject = JSONUtil.parseObj(jsonContent);
                res = (List<Map<String, String>>) jsonObject.get("result");
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }
        return res.stream()
            .filter(x-> x.get("DBTYPE").equals(dbType))
            .map(x-> x.get("DBURL"))
            .distinct()
            .collect(Collectors.toList());
    }
}
